package dao;

import vo.TuitionRecord;

import java.sql.*;
import java.util.ArrayList;

public class TuitionRecordDao {
    private Connection conn = null;

    private void initConnection() throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/studentManagerDB?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true",
                "StuManager", "123456");
    }

    private void closeConnection() throws SQLException {
        conn.close();
    }

    public void addTuitionRecord(TuitionRecord tuitionRecord) throws Exception {
        initConnection();
        String sql = "INSERT INTO tuition_records(student_id, student_name, payment_date, amount) VALUES (?, ?, ?, ?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, tuitionRecord.getStudentId());
        ps.setString(2, tuitionRecord.getStudentName());
        ps.setDate(3, Date.valueOf(tuitionRecord.getPaymentDate()));
        ps.setDouble(4, tuitionRecord.getAmount());
        ps.executeUpdate();
        closeConnection();
    }

    public ArrayList<TuitionRecord> getTuitionRecordsByStudentId(String studentId) throws Exception {
        initConnection();
        String sql = "SELECT * FROM tuition_records WHERE student_id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, studentId);
        ResultSet rs = ps.executeQuery();
        ArrayList<TuitionRecord> tuitionRecords = new ArrayList<>();
        while (rs.next()) {
            TuitionRecord record = new TuitionRecord();
            record.setStudentId(rs.getString("student_id"));
            record.setStudentName(rs.getString("student_name"));
            record.setPaymentDate(rs.getDate("payment_date").toLocalDate());
            record.setAmount(rs.getDouble("amount"));
            tuitionRecords.add(record);
        }
        closeConnection();
        return tuitionRecords;
    }

    public double calculateTotalTuitionForStudent(String studentId) throws Exception {
        initConnection();
        String sql = "SELECT SUM(amount) AS total_amount FROM tuition_records WHERE student_id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, studentId);
        ResultSet rs = ps.executeQuery();
        double totalAmount = 0;
        if (rs.next()) {
            totalAmount = rs.getDouble("total_amount");
        }
        closeConnection();
        return totalAmount;
    }



    public boolean updateTuitionRecord(TuitionRecord tuitionRecord) throws Exception {
        initConnection();
        String sql = "UPDATE tuition_records SET student_id = ?, student_name = ?, payment_date = ?, amount = ? WHERE record_id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, tuitionRecord.getStudentId());
        ps.setString(2, tuitionRecord.getStudentName());
        ps.setDate(3, Date.valueOf(tuitionRecord.getPaymentDate()));
        ps.setDouble(4, tuitionRecord.getAmount());
        int updatedRows = ps.executeUpdate();
        closeConnection();
        return updatedRows > 0;
    }

    public ArrayList<TuitionRecord> findAll() throws Exception{
        initConnection();
        String sql = "SELECT * FROM tuition_records";
        ArrayList<TuitionRecord> tuitionRecords = new ArrayList<>();
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            TuitionRecord record = new TuitionRecord();
            record.setRecordId(rs.getInt("record_id"));
            record.setStudentId(rs.getString("student_id"));
            record.setStudentName(rs.getString("student_name"));
            record.setPaymentDate(rs.getDate("payment_date").toLocalDate());
            record.setAmount(rs.getDouble("amount"));
            tuitionRecords.add(record);
        }
        closeConnection();
        rs.close();
        return tuitionRecords;
    }



    public boolean deleteTuitionRecord(String id) throws Exception{
        initConnection();
        String sql = "DELETE FROM tuition_records WHERE record_id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, id);
        int deletedRows = ps.executeUpdate();
        closeConnection();
        return deletedRows > 0;
    }

    public int getCount() throws Exception{
        initConnection();
        String sql = "SELECT COUNT(*) FROM tuition_records";
        int count = 0;
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            count = rs.getInt(1);
        }
        closeConnection();
        rs.close();
        return count;
    }

    public ArrayList<TuitionRecord> getOnePage(int currentIndex, int size) throws Exception{
        initConnection();
        String sql = "SELECT * FROM tuition_records LIMIT ?, ?";
        ArrayList<TuitionRecord> tuitionRecords = new ArrayList<>();
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, (currentIndex - 1) * size);
        ps.setInt(2, size);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            TuitionRecord record = new TuitionRecord();
            record.setRecordId(rs.getInt("record_id"));
            record.setStudentId(rs.getString("student_id"));
            record.setStudentName(rs.getString("student_name"));
            record.setPaymentDate(rs.getDate("payment_date").toLocalDate());
            record.setAmount(rs.getDouble("amount"));
            tuitionRecords.add(record);
        }
        closeConnection();
        rs.close();
        return tuitionRecords;
    }

    public TuitionRecord findWithId(String key) throws Exception{
        initConnection();
        String sql = "SELECT * FROM tuition_records WHERE record_id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, key);
        ResultSet rs = ps.executeQuery();
        TuitionRecord record = null;
        if (rs.next()) {
            record = new TuitionRecord();
            record.setRecordId(rs.getInt("record_id"));
            record.setStudentId(rs.getString("student_id"));
            record.setStudentName(rs.getString("student_name"));
            record.setPaymentDate(rs.getDate("payment_date").toLocalDate());
            record.setAmount(rs.getDouble("amount"));
        }
        closeConnection();
        rs.close();
        return record;
    }
}
